using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using PropertyManagement.Core.Model;

namespace PropertyManagement.Core.DataAccess {
        public static partial class HouseOwnerDataAccess {
             
            public static HouseOwner GetById(int id){
                var sql = @"select * from dbo.HouseOwner where HouseOwnerID = @id ";
                var paramList = new[] {
                    new SqlParameter("id", id)
                };
                using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql,
                                                                paramList)) {
                    if (iReader.Read( )) {
                            return CreationHouseOwnerByIReader(iReader);
                    };
                    return null;
                }
            }

            public static void Add(HouseOwner entity){
                  var sql = @"insert into dbo.HouseOwner values (@housecode,@apartmentname,@floor,@housetype,@spacearea,@ownername,@ownerphonenumber,@owneridentitycard,@remark,@effectivedate,@creationdate,@lastupdate,@creationuserid,@lastactionuserid) ;   select SCOPE_IDENTITY (); ";
                  var paramList = new SqlParameter[] {
							new SqlParameter("apartmentname",entity.ApartmentName),
							new SqlParameter("creationdate",entity.CreationDate),
							new SqlParameter("creationuserid",entity.CreationUserID),
							new SqlParameter("effectivedate",entity.EffectiveDate),
							new SqlParameter("floor",entity.Floor),
							new SqlParameter("housecode",entity.HouseCode),
							new SqlParameter("housetype",entity.HouseType),
							new SqlParameter("lastactionuserid",entity.LastActionUserID),
							new SqlParameter("lastupdate",entity.LastUpdate),
							new SqlParameter("owneridentitycard",entity.OwnerIdentityCard),
							new SqlParameter("ownername",entity.OwnerName),
							new SqlParameter("ownerphonenumber",entity.OwnerPhoneNumber),
							new SqlParameter("remark",entity.Remark),
							new SqlParameter("spacearea",entity.SpaceArea)
                        };
                  using ( var ireader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql,
                                                            paramList)) {
                        if (ireader.Read()) {
                            entity.HouseOwnerID = int.Parse(ireader[0].ToString());
                        }
                  }
            
            }   

            public static void Remove(HouseOwner entity) {
                var sql = @"delete from dbo.HouseOwner where HouseOwnerID = @id";
                var paramList = new[] {
                    new SqlParameter("id", entity.HouseOwnerID)
                };
                SqlServerHelper.ExecuteNonQuery(SqlServerHelper.ConnectionString, CommandType.Text, sql, paramList);
            }   

            public static IEnumerable<HouseOwner> LoadAll(){
                    var sql = " select * from dbo.HouseOwner ";
                    var list = new List<HouseOwner>( );
                    using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql)) {
                        while (iReader.Read( )) {
                                list.Add(CreationHouseOwnerByIReader(iReader));
                        };
                    }  
                    return list;
            }   
        
            public static void Update(HouseOwner entity){
                var sql = @" update dbo.HouseOwner set ApartmentName=@apartmentname,CreationDate=@creationdate,CreationUserID=@creationuserid,EffectiveDate=@effectivedate,Floor=@floor,HouseCode=@housecode,HouseType=@housetype,LastActionUserID=@lastactionuserid,LastUpdate=@lastupdate,OwnerIdentityCard=@owneridentitycard,OwnerName=@ownername,OwnerPhoneNumber=@ownerphonenumber,Remark=@remark,SpaceArea=@spacearea where HouseOwnerID = @id ";
                var paramList = new SqlParameter[] {
                            new SqlParameter("id",entity.HouseOwnerID),
							new SqlParameter("apartmentname",entity.ApartmentName),
							new SqlParameter("creationdate",entity.CreationDate),
							new SqlParameter("creationuserid",entity.CreationUserID),
							new SqlParameter("effectivedate",entity.EffectiveDate),
							new SqlParameter("floor",entity.Floor),
							new SqlParameter("housecode",entity.HouseCode),
							new SqlParameter("housetype",entity.HouseType),
							new SqlParameter("lastactionuserid",entity.LastActionUserID),
							new SqlParameter("lastupdate",entity.LastUpdate),
							new SqlParameter("owneridentitycard",entity.OwnerIdentityCard),
							new SqlParameter("ownername",entity.OwnerName),
							new SqlParameter("ownerphonenumber",entity.OwnerPhoneNumber),
							new SqlParameter("remark",entity.Remark),
							new SqlParameter("spacearea",entity.SpaceArea)
                        };
                SqlServerHelper.ExecuteNonQuery(SqlServerHelper.ConnectionString, CommandType.Text, sql, paramList);
            }
            
            public static bool Exists(HouseOwner entity){
                return entity.HouseOwnerID != 0 && GetById(entity.HouseOwnerID)!=null;
            }


            private static HouseOwner CreationHouseOwnerByIReader(IDataReader iReader){
                if(iReader!=null){
                      return new HouseOwner{
							ApartmentName = iReader["ApartmentName"] is DBNull ? default(String) : (String)iReader["ApartmentName"] ,
							CreationDate = iReader["CreationDate"] is DBNull ? default(DateTime) : (DateTime)iReader["CreationDate"] ,
							CreationUserID = iReader["CreationUserID"] is DBNull ? default(Int32) : (Int32)iReader["CreationUserID"] ,
							EffectiveDate = iReader["EffectiveDate"] is DBNull ? default(DateTime) : (DateTime)iReader["EffectiveDate"] ,
							Floor = iReader["Floor"] is DBNull ? default(Int32) : (Int32)iReader["Floor"] ,
							HouseCode = iReader["HouseCode"] is DBNull ? default(String) : (String)iReader["HouseCode"] ,
							HouseOwnerID = iReader["HouseOwnerID"] is DBNull ? default(Int32) : (Int32)iReader["HouseOwnerID"] ,
							HouseType = iReader["HouseType"] is DBNull ? default(HouseType) : (HouseType)iReader["HouseType"] ,
							LastActionUserID = iReader["LastActionUserID"] is DBNull ? default(Int32) : (Int32)iReader["LastActionUserID"] ,
							LastUpdate = iReader["LastUpdate"] is DBNull ? default(DateTime) : (DateTime)iReader["LastUpdate"] ,
							OwnerIdentityCard = iReader["OwnerIdentityCard"] is DBNull ? default(String) : (String)iReader["OwnerIdentityCard"] ,
							OwnerName = iReader["OwnerName"] is DBNull ? default(String) : (String)iReader["OwnerName"] ,
							OwnerPhoneNumber = iReader["OwnerPhoneNumber"] is DBNull ? default(String) : (String)iReader["OwnerPhoneNumber"] ,
							Remark = iReader["Remark"] is DBNull ? default(String) : (String)iReader["Remark"] ,
							SpaceArea = iReader["SpaceArea"] is DBNull ? default(Int32) : (Int32)iReader["SpaceArea"] 
                        };
                }
                return null; 
            }

            
            
            public static HouseOwner GetByPKs(HouseOwner entity){
                var sql =  @"select *  from dbo.HouseOwner where HouseCode = @housecode ";
                var paramList = new SqlParameter[] {
							new SqlParameter("housecode",entity.HouseCode)
                        };
                 using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql,
                                                                paramList)) {
                    if (iReader.Read( )) {
                            return CreationHouseOwnerByIReader(iReader);
                    };
                    return null;
                }
            }

            public static bool ExistsByPKs(HouseOwner entity){
                var sql =  @"select *  from dbo.HouseOwner where HouseCode = @housecode ";
                var paramList = new SqlParameter[] {
							new SqlParameter("housecode",entity.HouseCode)
                        };
                 using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql,
                                                                paramList)) {
                    return iReader.Read( );
                }
            }

    }
}